﻿using MqttAnalyze.Data.Common;
using MqttAnalyze.Data.Dal;
using MqttAnalyze.Data.Models;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Security.AccessControl;
using System.Text;
using System.Threading.Tasks;

namespace MqttAnalyze.Data.Blls
{
    public class MqttDataBLL : IMqttDataBLL
    {
        private readonly IMqttDbDal mqttDal;
        /// <summary>
        /// sqlite 单个读写
        /// </summary>
        private object lockObject;
        public MqttDataBLL(string dbLocation)
        {
            string connectStr = string.Format("data source={0};Pooling=true;", dbLocation);
            this.mqttDal = new MqttDbDal(connectStr);
            lockObject = new object();
        }

        #region 发送消息模板
        public DataResult GetMsgTemplateList()
        {
            var rst = new DataResult();
            lock (lockObject)
            {
                try
                {
                    mqttDal.OpenQuery();
                    SugarParameter[] paramArr = new SugarParameter[0];
                    string querySql = "SELECT TPL_ID,TPL_NAME,TOPIC,CONTENT,IS_RETAIN,QOS FROM TB_MSG_TEMPLATE ;";
                    rst.ErrorCode = mqttDal.QueryDataFromSql(querySql, paramArr, out DataTable userDt);
                    List<MqttMessageTemplateTb> dataList = new List<MqttMessageTemplateTb>();
                    if (rst.ErrorCode == SqlErrorCode.KSuccess)
                    {
                        for (int i = 0; i < userDt.Rows.Count; i++)
                        {
                            var dto = new MqttMessageTemplateTb();
                            var rowData = userDt.Rows[i];
                            dto.TemplateId = rowData["TPL_ID"]?.ToString() ?? "";
                            dto.TemplateName = rowData["TPL_NAME"]?.ToString() ?? "";
                            dto.Topic = rowData["TOPIC"]?.ToString() ?? "";
                            dto.Message = rowData["CONTENT"]?.ToString() ?? "";
                            dto.IsRetain = ConvertHelper.ToInt32(rowData["IS_RETAIN"]) == 1;
                            dto.Qos = ConvertHelper.ToInt32(rowData["QOS"]);
                            dataList.Add(dto);
                        }
                        rst.Data = dataList;
                    }
                }
                catch (Exception ex)
                {
                    rst.ErrorCode = SqlErrorCode.KInternalException;
                }
                finally
                {
                    mqttDal.CloseQuery();
                }
            }
            return rst;
        }

        public DataResult SaveMsgTemplate(MqttMessageTemplateTb tpl)
        {
            var rst = new DataResult();
            lock (lockObject)
            {
                try
                {
                    mqttDal.OpenQuery();
                    SugarParameter[] paramArr = new SugarParameter[6];
                    paramArr[0] = new SugarParameter("@id", tpl.TemplateId);
                    paramArr[1] = new SugarParameter("@name", tpl.TemplateName);
                    paramArr[2] = new SugarParameter("@topic", tpl.Topic);
                    paramArr[3] = new SugarParameter("@content", tpl.Message);
                    paramArr[4] = new SugarParameter("@retain", tpl.IsRetain ? 1 : 0);
                    paramArr[5] = new SugarParameter("@qos", tpl.Qos);
                    string querySql = "SELECT TPL_ID FROM TB_MSG_TEMPLATE WHERE TPL_NAME=@name;";
                    rst.ErrorCode = mqttDal.QueryDataFromSql(querySql, paramArr, out DataTable userDt);
                    if (rst.ErrorCode != SqlErrorCode.KSuccess)
                    {
                        return rst;
                    }
                    if (userDt.Rows.Count > 0)
                    {
                        string existsId = userDt.Rows[0]["TPL_ID"]?.ToString() ?? "";
                        if (!string.IsNullOrEmpty(tpl.TemplateId) && tpl.TemplateId != existsId)
                        {
                            rst.ErrorCode = SqlErrorCode.KDuplicate;
                            return rst;
                        }
                        //更新
                        tpl.TemplateId = existsId;
                        var affectRows = mqttDal.GetClient().Updateable<MqttMessageTemplateTb>(tpl).ExecuteCommand();
                        if (affectRows > 0)
                        {
                            rst.ErrorCode = SqlErrorCode.KSuccess;
                            rst.Data = existsId;
                        }
                        else
                        {
                            rst.ErrorCode = SqlErrorCode.KFail;
                        }
                    }
                    else
                    {
                        if (string.IsNullOrEmpty(tpl.TemplateId))
                        {
                            tpl.TemplateId = Guid.NewGuid().ToString("N");
                        }
                        //插入
                        var affectRows = mqttDal.GetClient().Insertable<MqttMessageTemplateTb>(tpl).ExecuteCommand();
                        if (affectRows > 0)
                        {
                            rst.ErrorCode = SqlErrorCode.KSuccess;
                            rst.Data = tpl.TemplateId;
                        }
                        else
                        {
                            rst.ErrorCode = SqlErrorCode.KFail;
                        }
                    }
                }
                catch (Exception ex)
                {
                    rst.ErrorCode = SqlErrorCode.KInternalException;
                }
                finally
                {
                    mqttDal.CloseQuery();
                }
            }
            return rst;
        }

        public DataResult RemoveMsgTemplate(string tplId)
        {
            var rst = new DataResult();
            lock (lockObject)
            {
                try
                {
                    mqttDal.OpenQuery();
                    SugarParameter[] paramArr = new SugarParameter[1];
                    paramArr[0] = new SugarParameter("@id", tplId);
                    string updateSql = "DELETE FROM TB_MSG_TEMPLATE WHERE TPL_ID=@id;";
                    rst.ErrorCode = mqttDal.ExecuteSqlCommand(updateSql, paramArr);
                }
                catch (Exception ex)
                {
                    rst.ErrorCode = SqlErrorCode.KInternalException;
                }
                finally
                {
                    mqttDal.CloseQuery();
                }
            }
            return rst;
        }

        #endregion

        public DataResult GetMsgCategoryList()
        {
            var rst = new DataResult();
            lock (lockObject)
            {
                try
                {
                    mqttDal.OpenQuery();
                    SugarParameter[] paramArr = new SugarParameter[0];
                    string querySql = "SELECT CAT_ID,TOPIC,CATEGORY,SAVE_NUM FROM TB_RECVMSG_CATEGORY;";
                    rst.ErrorCode = mqttDal.QueryDataFromSql(querySql, paramArr, out DataTable categoryDt);
                    List<RecvMessageCategory> dataList = new List<RecvMessageCategory>();
                    if (rst.ErrorCode == SqlErrorCode.KSuccess)
                    {
                        for (int i = 0; i < categoryDt.Rows.Count; i++)
                        {
                            var dto = new RecvMessageCategory();
                            var rowData = categoryDt.Rows[i];
                            dto.CategoryId = rowData["CAT_ID"]?.ToString() ?? "";
                            dto.Topic = rowData["TOPIC"]?.ToString() ?? "";
                            dto.Classify = rowData["CATEGORY"]?.ToString() ?? "";
                            dto.KeepNum = ConvertHelper.ToInt32(rowData["SAVE_NUM"]);
                            dataList.Add(dto);
                        }
                    }
                    querySql = "SELECT SUB_CLASS,CONTENT,RECV_TIME FROM TB_RECVMSG WHERE CAT_ID=@id ORDER BY RECV_TIME ASC LIMIT {0};";
                    foreach (var item in dataList)
                    {
                        paramArr = new SugarParameter[1];
                        paramArr[0] = new SugarParameter("@id", item.CategoryId);                     
                        string subSql = "SELECT DISTINCT SUB_CLASS FROM TB_RECVMSG WHERE CAT_ID=@id;";
                        var queryCode = mqttDal.QueryDataFromSql(subSql, paramArr, out DataTable subDt);
                        if (queryCode == SqlErrorCode.KSuccess)
                        {
                            for (int i = 0; i < subDt.Rows.Count; i++)
                            {
                                item.SubClass.Add(subDt.Rows[i][0]?.ToString() ?? "");
                            }
                        }
                        string tplSql = "SELECT SUB_CLASS,CONTENT,RECV_TIME FROM TB_RECVMSG WHERE CAT_ID=@id ORDER BY RECV_TIME ASC;";
                        if (item.KeepNum > 0)
                        {
                            tplSql = string.Format(querySql, item.KeepNum);
                        }
                        queryCode = mqttDal.QueryDataFromSql(tplSql, paramArr, out DataTable msgDt);
                        if (queryCode == SqlErrorCode.KSuccess)
                        {
                            item.MsgList = new List<MqttMessage>();
                            for (int i = 0; i < msgDt.Rows.Count; i++)
                            {
                                var dto = new MqttMessage();
                                var rowData = msgDt.Rows[i];
                                dto.SubClass= rowData["SUB_CLASS"]?.ToString() ?? "";
                                dto.Content = rowData["CONTENT"]?.ToString() ?? "";
                                dto.RecvTime = rowData["RECV_TIME"]?.ToString() ?? "";
                                item.MsgList.Add(dto);
                            }
                        }
                    }
                    rst.Data = dataList;
                }
                catch (Exception ex)
                {
                    rst.ErrorCode = SqlErrorCode.KInternalException;
                }
                finally
                {
                    mqttDal.CloseQuery();
                }
            }
            return rst;
        }

        public DataResult SaveMsgCategory(RecvTopicCategory recvTopic)
        {
            var rst = new DataResult();
            lock (lockObject)
            {
                try
                {
                    mqttDal.OpenQuery();
                    SugarParameter[] paramArr = new SugarParameter[4];
                    string querySql = "SELECT CAT_ID FROM TB_RECVMSG_CATEGORY WHERE TOPIC=@topic;";

                    paramArr[0] = new SugarParameter("@id", recvTopic.CategoryId??"");
                    paramArr[1] = new SugarParameter("@topic", recvTopic.Topic);
                    paramArr[2] = new SugarParameter("@cat", recvTopic.Classify);
                    paramArr[3] = new SugarParameter("@num", recvTopic.KeepNum);
                    rst.ErrorCode = mqttDal.QueryDataFromSql(querySql, paramArr, out DataTable userDt);
                    if (rst.ErrorCode != SqlErrorCode.KSuccess)
                    {
                        return rst;
                    }
                    if (userDt.Rows.Count > 0)
                    {
                        string catId = userDt.Rows[0]["CAT_ID"]?.ToString() ?? "";
                        if (!string.IsNullOrEmpty(recvTopic.CategoryId) &&recvTopic.CategoryId != catId)
                        {
                            rst.ErrorCode = SqlErrorCode.KDuplicate;
                            return rst;
                        }
                        //更新
                        string updateSql = "UPDATE TB_RECVMSG_CATEGORY SET SAVE_NUM=@num,CATEGORY=@cat WHERE CAT_ID=@id;";
                        rst.ErrorCode = mqttDal.ExecuteSqlCommand(updateSql, paramArr);
                        rst.Data = catId;
                    }
                    else
                    {
                        var newItem = new MqttMessageCategoryTb();
                        newItem.Category = recvTopic.Classify;
                        newItem.Topic = recvTopic.Topic;
                        newItem.KeepNum = recvTopic.KeepNum;
                        newItem.CategoryId = recvTopic.CategoryId??"";
                        if (string.IsNullOrEmpty(recvTopic.CategoryId))
                        {
                            newItem.CategoryId = Guid.NewGuid().ToString("N");
                        }
                        //插入
                        var affectRows = mqttDal.GetClient().Insertable<MqttMessageCategoryTb>(newItem).ExecuteCommand();
                        if (affectRows > 0)
                        {
                            rst.ErrorCode = SqlErrorCode.KSuccess;
                            rst.Data = newItem.CategoryId;
                        }
                        else
                        {
                            rst.ErrorCode = SqlErrorCode.KFail;
                        }
                    }
                }
                catch (Exception ex)
                {
                    rst.ErrorCode = SqlErrorCode.KInternalException;
                }
                finally
                {
                    mqttDal.CloseQuery();
                }
            }
            return rst;
        }

        public DataResult SaveRecvMsg(MqttRecvMessage msg)
        {
            var rst = new DataResult();
            lock (lockObject)
            {
                try
                {
                    //List<MqttRecvMessageTb> msgList = new List<MqttRecvMessageTb>();
                    var ent = new MqttRecvMessageTb();
                    ent.MessageId = Guid.NewGuid().ToString("N");
                    ent.CategoryId = msg.CategoryId;
                    ent.Topic = msg.Topic;
                    ent.SubClass = msg.SubClass;
                    ent.Message = msg.Content;
                    ent.RecvTime = msg.RecvTime;
                    //   msgList.Add(ent);
                    //SqlSugar 批量插入
                    //mqttDal.GetClient().Fastest<MqttRecvMessageTb>().BulkCopy(msgList);
                    mqttDal.GetClient().Insertable<MqttRecvMessageTb>(ent).ExecuteCommand();
                    rst.ErrorCode = SqlErrorCode.KSuccess;
                }
                catch (Exception ex)
                {
                    rst.ErrorCode = SqlErrorCode.KInternalException;
                }
                finally
                {
                    mqttDal.CloseQuery();
                }
            }
            return rst;
        }

        public DataResult RemoveMsgCategory(string catId)
        {
            var rst = new DataResult(); lock (lockObject)
            {
                try
                {
                    mqttDal.OpenQuery();
                    SugarParameter[] paramArr = new SugarParameter[1];
                    paramArr[0] = new SugarParameter("@id", catId);
                    string updateSql = "DELETE FROM TB_RECVMSG_CATEGORY WHERE CAT_ID=@id;DELETE FROM TB_RECVMSG WHERE CAT_ID=@id;";
                    rst.ErrorCode = mqttDal.ExecuteSqlCommand(updateSql, paramArr);
                }
                catch (Exception ex)
                {
                    rst.ErrorCode = SqlErrorCode.KInternalException;
                }
                finally
                {
                    mqttDal.CloseQuery();
                }
            }
            return rst;
        }
    }
}
